﻿Imports System.Data
Imports System.Data.SqlClient
Imports Entidad

Public Class DocumentoClienteDetalleDatos

    Function obtenerDetallePedidoXART(codArticulo As String, ByVal codPedido As String, tipoDOC As String, codSucursal As String, codCia As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "SELECT dcd.CIA,dcd.SUC,dcd.TDOC_Codigo,dcd.DCI_Codigo,DCD_Codigo,ALM_Codigo,dcd.ART_Codigo,DCD_Ordenado,DCD_Atendido, " &
                              "DCD_Descuento,DCD_DsctoAdicional,DCD_ValorUnitSoles,DCD_UltModificacion,DCD_DCDCodigo, " &
                              "DCD_ValorUnitDolares,DCD_DsctoCond,DCD_PorcPerc,DCD_Activo,a.ART_Descripcion, " &
                              "(CASE WHEN dc.MON_Codigo = '01' THEN a.ART_PrecioSoles ELSE a.ART_PrecioDolares END) AS Precio, " &
                              "DCD_Subtotal,DCD_MontoPerc,a.ART_Percepcion,a.ART_ZEmergencia,um.UNM_Abreviatura " &
                              "FROM Documento_Cliente_Detalle dcd " &
                              "INNER JOIN Articulo a " &
                              "ON a.ART_Codigo = dcd.ART_Codigo AND a.ART_Activo = '1' " &
                              "INNER JOIN UnidadMedida um " &
                              "ON um.UNM_Codigo = a.UNM_Codigo AND um.UNM_Activo = '1' " &
                              "INNER JOIN Documento_Cliente dc " &
                              "ON dc.CIA = dcd.CIA AND dc.SUC_Codigo = dcd.SUC AND dc.TDOC_Codigo = dcd.TDOC_Codigo " &
                              "AND dc.DCI_Codigo = dcd.DCI_Codigo " &
                              "WHERE dcd.CIA = @CIA AND dcd.SUC = @SUC AND dcd.TDOC_Codigo = @TDOC " &
                              "AND dcd.DCI_Codigo = @DCI AND DCD_Activo = '1' AND dcd.ART_Codigo = @ART"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@CIA", codCia)
            .Parameters.AddWithValue("@SUC", codSucursal)
            .Parameters.AddWithValue("@TDOC", tipoDOC)
            .Parameters.AddWithValue("@DCI", codPedido)
            .Parameters.AddWithValue("@ART", codArticulo)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function obtenerDetallePedido(ByVal codPedido As String, tipoDOC As String, codSucursal As String, codCia As String, activo As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "SELECT dcd.CIA,dcd.SUC,dcd.TDOC_Codigo,dcd.DCI_Codigo,DCD_Codigo,ALM_Codigo,dcd.ART_Codigo,DCD_Ordenado,DCD_Atendido, " &
                              "DCD_Descuento,DCD_DsctoAdicional,DCD_ValorUnitSoles,DCD_UltModificacion,DCD_DCDCodigo, " &
                              "DCD_ValorUnitDolares,DCD_DsctoCond,DCD_PorcPerc,DCD_Activo,a.ART_Descripcion, " &
                              "(CASE WHEN dc.MON_Codigo = '01' THEN DCD_ValorUnitSoles ELSE DCD_ValorUnitDolares END) AS Precio, " &
                              "DCD_Subtotal,DCD_MontoPerc,a.ART_Percepcion,a.ART_ZEmergencia,um.UNM_Abreviatura, dcd.DCD_DsctoAdicional " &
                              "FROM Documento_Cliente_Detalle dcd " &
                              "INNER JOIN Articulo a " &
                              "ON a.ART_Codigo = dcd.ART_Codigo AND a.ART_Activo = '1' " &
                              "INNER JOIN UnidadMedida um " &
                              "ON um.UNM_Codigo = a.UNM_Codigo AND um.UNM_Activo = '1' " &
                              "INNER JOIN Documento_Cliente dc " &
                              "ON dc.CIA = dcd.CIA AND dc.SUC_Codigo = dcd.SUC AND dc.TDOC_Codigo = dcd.TDOC_Codigo " &
                              "AND dc.DCI_Codigo = dcd.DCI_Codigo " &
                              "WHERE dcd.CIA = @CIA AND dcd.SUC = @SUC AND dcd.TDOC_Codigo = @TDOC " &
                              "AND dcd.DCI_Codigo = @DCI AND DCD_Activo LIKE '%" & activo & "%'"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@CIA", codCia)
            .Parameters.AddWithValue("@SUC", codSucursal)
            .Parameters.AddWithValue("@TDOC", tipoDOC)
            .Parameters.AddWithValue("@DCI", codPedido)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Sub dismuinuirCantidadDetalle(ByVal cant As String, ByVal codPed As String, ByVal codPDD As String, tipoDOC As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "UPDATE Documento_Cliente_Detalle " &
                              "SET DCD_Atendido = DCD_Atendido - @cantidad " &
                              "WHERE DCI_Codigo = @codDCI AND " &
                              "DCD_Codigo = @codDCD " &
                              "AND TDOC_Codigo = @tipo"
        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@cantidad", cant)
            .Parameters.AddWithValue("@codDCI", codPed)
            .Parameters.AddWithValue("@codDCD", codPDD)
            .Parameters.AddWithValue("@tipo", tipoDOC)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Sub aumentarCantidadDetalle(ByVal pdp As PedidoDetallePickeador, tipoDOC As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "UPDATE Documento_Cliente_Detalle " &
                              "SET DCD_Atendido = DCD_Atendido + @cantidad " &
                              "WHERE DCI_Codigo = @codDCI AND DCD_Codigo = @codDCD " &
                              "AND TDOC_Codigo = @tipo"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@cantidad", pdp.PDP_Cantidad)
            .Parameters.AddWithValue("@codDCI", pdp.PED_Codigo)
            .Parameters.AddWithValue("@codDCD", pdp.PDD_Codigo)
            .Parameters.AddWithValue("@tipo", tipoDOC)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Function listarDetallePendienteAsignar(cia As String, suc As String, tipoDOC As String, ByVal codPedido As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT dcd.DCD_Codigo,a.ART_Codigo,a.ART_Descripcion " &
                              "AS Articulo, a.ART_UM AS Um, " &
                              "dcd.DCD_Atendido - ISNULL(SUM(dadp.DADP_Cantidad),0) AS Cantidad " &
                              "FROM Documento_Cliente_Detalle dcd " &
                              "INNER JOIN  articulo a ON dcd.ART_Codigo = a.ART_Codigo AND dcd.DCD_Activo = '1' " &
                              "LEFT JOIN Documento_Cliente_Almacen_Detalle dcad ON dcad.CIA_Cliente = dcd.CIA AND dcad.SUC_Cliente = dcd.SUC " &
                              "AND dcad.TDOC_Cliente = dcd.TDOC_Codigo AND dcad.DCI_Codigo = dcd.DCI_Codigo " &
                              "AND dcad.DCD_Codigo = dcd.DCD_Codigo and dcad.DCAD_Activo='1' " &
                              "LEFT JOIN Documento_Almacen_Detalle dad ON dad.CIA = dcad.CIA_Almacen AND dad.SUC = dcad.SUC_Almacen " &
                              " AND dad.TDOC_Codigo = dcad.TDOC_Almacen AND dad.DCA_Codigo = dcad.DCA_Codigo AND dad.DAD_Codigo = dcad.DAD_Codigo " &
                              " AND dad.DAD_Activo='1' " & _
                              "LEFT JOIN Documento_Almacen_Detalle_Pickeador dadp ON dadp.CIA = dad.CIA AND dadp.SUC_Codigo = dad.SUC " &
                              "AND dadp.TDOC_Codigo = dad.TDOC_Codigo AND dadp.DCA_Codigo = dad.DCA_Codigo AND dadp.DAD_Codigo = dad.DAD_Codigo " &
                              "AND dadp.DADP_Activo = '1' " &
                              "WHERE dcd.DCI_Codigo = '" & codPedido & "' AND dcd.CIA = '" & cia & "' AND dcd.SUC = '" & suc & "' AND dcd.TDOC_Codigo = '" & tipoDOC & "' " &
                              "GROUP BY dcd.DCD_Codigo,a.ART_Codigo,a.ART_Descripcion,a.ART_UM, dcd.DCD_Atendido " &
                              "HAVING dcd.DCD_Atendido - ISNULL(SUM(dadp.DADP_Cantidad),0) > 0"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)
        coneccion.Close()

        Return ds
    End Function

    Function obtenerUltimoCodigoDetalle(ByVal codPed As String, tipoDOC As String) As Integer
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT TOP 1 DCD_Codigo " &
                              "FROM Documento_Cliente_Detalle " &
                              "WHERE DCI_Codigo = '" & codPed & "' " &
                              "AND TDOC_Codigo = '" & tipoDOC & "' " &
                              "ORDER BY DCD_Codigo DESC"
        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return CInt(ds.Tables(0).Rows(0)("PDD_Codigo").ToString.Trim)
    End Function

    Function verificarAsignarDetalle(codPedido As String, tipoDOC As String, codSucursal As String, codCia As String) As Boolean
        Dim valor As Boolean = False
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT DCD_Codigo " &
                              "FROM Documento_Cliente_Detalle " &
                              "WHERE DCI_Codigo = '" & codPedido & "' " &
                              "AND TDOC_Codigo = '" & tipoDOC & "' " &
                              "AND DCD_Atendido = 0"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        If obtenerDetallePedido(codPedido, tipoDOC, codSucursal, codCia, "1").Tables(0).Rows.Count = ds.Tables(0).Rows.Count Then
            valor = True
        End If

        Return valor
    End Function

    Sub registrarDocumentoReboteDetalle(codDocumento As String, codPedido As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "INSERT INTO Documento_Cliente_Detalle " &
                              "SELECT CIA,SUC,'R',@COD,DCD_Codigo,NULL,ART_Codigo,ALM_Codigo, " &
                              "DCD_Ordenado,DCD_Atendido,NULL,DCD_ValorUnit,GETDATE(),NULL, " &
                              "NULL,NULL,NULL,NULL,NULL,NULL,'1' " &
                              "FROM Documento_Cliente_Detalle " &
                              "WHERE DCI_Codigo = @DOC AND DCD_Activo = '1'"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@COD", codDocumento)
            .Parameters.AddWithValue("@DOC", codPedido)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Sub registrarDocumentoReboteDetalle(codDocumento As String, codPedido As String, codDetalle As String, cantidad As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "INSERT INTO Documento_Cliente_Detalle " &
                              "SELECT CIA,SUC,'R',@COD,DCD_Codigo,NULL,ART_Codigo,ALM_Codigo, " &
                              "DCD_Ordenado,@CANT,NULL,DCD_ValorUnit,GETDATE(),NULL, " &
                              "NULL,NULL,NULL,NULL,NULL,NULL,'1' " &
                              "FROM Documento_Cliente_Detalle " &
                              "WHERE DCI_Codigo = @DOC AND DCD_Codigo = @DOCD AND DCD_Activo = '1'"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@COD", codDocumento)
            .Parameters.AddWithValue("@DOCD", codDetalle)
            .Parameters.AddWithValue("@DOC", codPedido)
            .Parameters.AddWithValue("@CANT", cantidad)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Sub registrarDocumentoClienteDetalleRelacion(codPedido As String, codDocumento As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "INSERT INTO Documento_Cliente_Detalle_Rel " &
                              "SELECT CIA,SUC,TDOC_Codigo,DCI_Codigo,DCD_Codigo,CIA,SUC,'R',@COD,DCD_Codigo,'1' " &
                              "FROM Documento_Cliente_Detalle " &
                              "WHERE DCI_Codigo = @DOC " &
                              "AND DCD_Activo = '1'"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@COD", codDocumento)
            .Parameters.AddWithValue("@DOC", codPedido)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Sub registrarDocumentoClienteDetalleRelacion(relacion As DocumentoClienteDetalleRelacion)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "INSERT INTO Documento_Cliente_Detalle_Rel(CIA,SUC,TDOC_Codigo, " &
                              "DCI_Codigo,DCD_Codigo,CIA_R,SUC_R,TDOC_Codigo_R,DCI_Codigo_R,DCD_Codigo_R,DCDR_Activo) " &
                              "VALUES(@CIA,@SUC,@TDOC,@DCI,@DCD,@CIAR,@SUCR,@TDOCR,@DCIR,@DCDR,'1')"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@CIA", relacion.CIA)
            .Parameters.AddWithValue("@SUC", relacion.SUC)
            .Parameters.AddWithValue("@TDOC", relacion.TDOC_Codigo)
            .Parameters.AddWithValue("@DCI", relacion.DCI_Codigo)
            .Parameters.AddWithValue("@DCD", relacion.DCD_Codigo)
            .Parameters.AddWithValue("@CIAR", relacion.CIAR)
            .Parameters.AddWithValue("@SUCR", relacion.SUCR)
            .Parameters.AddWithValue("@TDOCR", relacion.TDOC_CodigoR)
            .Parameters.AddWithValue("@DCIR", relacion.DCI_CodigoR)
            .Parameters.AddWithValue("@DCDR", relacion.DCD_CodigoR)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Sub registrarDocumentoClienteDetalle(detalle As PedidoDetalle)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "INSERT INTO Documento_Cliente_Detalle(CIA,SUC,TDOC_Codigo,DCI_Codigo,DCD_Codigo,DCD_Fecha,ART_Codigo,ALM_Codigo, " &
                              "DCD_Ordenado,DCD_Atendido,DCD_Descuento,DCD_ValorUnit,DCD_UltModificacion,DCD_DsctoAdicional,DCD_ValorUnitSoles, " &
                              "DCD_ValorUnitDolares,DCD_DsctoCond,DCD_PorcPerc,DCD_FechasLlegada,DCD_Subtotal,DCD_MontoPerc,DCD_Activo) " &
                              "VALUES(@CIA,@SUC,@TDOC,@DCI,@DCD,GETDATE(),@ART,@ALM,@ORD,@ATEN,@DESC,@VALU,GETDATE(),@DSCTA,@VUS,@VUD,@DSCTC,@PP,GETDATE(),@SUB,@MPERC,@ACT)"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@CIA", detalle.CIA)
            .Parameters.AddWithValue("@SUC", detalle.SUC_Codigo)
            .Parameters.AddWithValue("@TDOC", detalle.TDOC_Codigo)
            .Parameters.AddWithValue("@DCI", detalle.DCI_Codigo)
            .Parameters.AddWithValue("@DCD", detalle.DCD_Codigo)
            .Parameters.AddWithValue("@ART", detalle.ART_Codigo)
            .Parameters.AddWithValue("@ALM", detalle.ALM_Codigo)
            .Parameters.AddWithValue("@ORD", detalle.DCD_Ordenado)
            .Parameters.AddWithValue("@ATEN", detalle.DCD_Atendido)
            .Parameters.AddWithValue("@DESC", detalle.DCD_Descuento)
            .Parameters.AddWithValue("@VALU", detalle.DCD_ValorUnitario)
            .Parameters.AddWithValue("@DSCTA", detalle.DCD_DescuentoAdicional)
            .Parameters.AddWithValue("@VUS", detalle.DCD_ValorUnitSoles)
            .Parameters.AddWithValue("@VUD", detalle.DCD_ValorUnitDolares)
            .Parameters.AddWithValue("@DSCTC", detalle.DCD_DescuentoCondicional)
            .Parameters.AddWithValue("@PP", detalle.DCD_PorcentaePercepcion)
            .Parameters.AddWithValue("@SUB", detalle.DCD_Subtotal)
            .Parameters.AddWithValue("@MPERC", detalle.DCD_MontoPercepcion)
            .Parameters.AddWithValue("@ACT", detalle.DCD_Activo)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Sub actualizarDocumentoClienteDetalle(detalle As PedidoDetalle)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "UPDATE Documento_Cliente_Detalle " &
                              "SET DCD_Activo = @ACT, DCD_Ordenado = @ORD, DCD_Atendido = @ATEN, " &
                              "DCD_Descuento = @DESC, DCD_ValorUnit = @VALU, " &
                              "DCD_UltModificacion = GETDATE(), DCD_DsctoAdicional = @DSCTA, " &
                              "DCD_ValorUnitSoles = @VUS, DCD_ValorUnitDolares = @VUD, " &
                              "DCD_DsctoCond = @DSCTC, DCD_PorcPerc = @PP, DCD_Subtotal = @SUB,DCD_MontoPerc = @MPERC " &
                              "WHERE CIA = @CIA AND SUC = @SUC AND TDOC_Codigo = @TDOC AND DCI_Codigo = @DCI AND DCD_Codigo = @DCD"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@ACT", detalle.DCD_Activo)
            .Parameters.AddWithValue("@ORD", detalle.DCD_Ordenado)
            .Parameters.AddWithValue("@ATEN", detalle.DCD_Atendido)
            .Parameters.AddWithValue("@DESC", detalle.DCD_Descuento)
            .Parameters.AddWithValue("@VALU", detalle.DCD_ValorUnitario)
            .Parameters.AddWithValue("@DSCTA", detalle.DCD_DescuentoAdicional)
            .Parameters.AddWithValue("@VUS", detalle.DCD_ValorUnitSoles)
            .Parameters.AddWithValue("@VUD", detalle.DCD_ValorUnitDolares)
            .Parameters.AddWithValue("@DSCTC", detalle.DCD_DescuentoCondicional)
            .Parameters.AddWithValue("@PP", detalle.DCD_PorcentaePercepcion)
            .Parameters.AddWithValue("@SUB", detalle.DCD_Subtotal)
            .Parameters.AddWithValue("@MPERC", detalle.DCD_MontoPercepcion)
            .Parameters.AddWithValue("@CIA", detalle.CIA)
            .Parameters.AddWithValue("@SUC", detalle.SUC_Codigo)
            .Parameters.AddWithValue("@TDOC", detalle.TDOC_Codigo)
            .Parameters.AddWithValue("@DCI", detalle.DCI_Codigo)
            .Parameters.AddWithValue("@DCD", detalle.DCD_Codigo)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Function obtenerStockXDetalle(codArticulo As String) As Decimal
        Dim stock As Decimal = 0
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "SELECT item,mano-compr AS Stock FROM OPENQUERY(GULDASYSBD, 'SELECT item,mano,compr FROM 1407/INVMAST') " &
                              "WHERE item = @ART"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@ART", codArticulo)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        If ds.Tables(0).Rows.Count > 0 Then
            stock = CDec(ds.Tables(0).Rows(0)("Stock").ToString.Trim)
        End If

        coneccion.Close()

        Return stock
    End Function

End Class
